<?php
class ModelSaleRequisition extends Model {
	public function getList($page = 1, $filter){
		$requisitionlist = M('requisition')
		->join('(select user_id,fullname as user_name from user) u on u.user_id = requisition.user_id','left')
		->join('(select count(1) as count, requisition_id,packing_no from requisition_product group by requisition_id) ppc on ppc.requisition_id = requisition.id','left')
		->order('requisition.id DESC')	
		->where($filter)->page($page)->limit($this->config->get('config_limit_admin'))
		->field("requisition.*,requisition_id,packing_no,(SELECT name from warehouse w where w.warehouse_id=requisition.out_warehouse_id ) as src_logcenter_name,(SELECT name from warehouse w where w.warehouse_id=requisition.in_warehouse_id ) as des_logcenter_name, count, user_name")
		->select();
		return $requisitionlist;
	}

	public function getSrcRequisitionOperationBySku($filter){
		$map['rp.sku'] = $filter['sku'];
		$map['_string'] = "requisition.deliver_time>='".$filter['filter_date_start']."' AND requisition.deliver_time<'".$filter['filter_date_end']."'";
		$map['requisition.status'] = array('in',array(count(getRequisitionStatus())-1,count(getRequisitionStatus())));
		$map['requisition.src_logcenter'] = $filter['filter_logcenter'];
		$src_requisition_operation = M('requisition_product')
		->alias('rp')
		->join('left join `requisition` ON requisition.id=rp.requisition_id')
		->join('left join `logcenters` lg ON requisition.user_id=lg.user_id')
		->where($map)
		->field('qty,name,unit_price,option_name,packing_no,deliver_time')
		->order('option_name,deliver_time')
		->select();
		return $src_requisition_operation;
	}

	public function getDesRequisitionOperationBySku($filter){
		$map['rp.sku'] = $filter['sku'];
		$map['_string'] = "requisition.deliver_time>='".$filter['filter_date_start']."' AND requisition.deliver_time<'".$filter['filter_date_end']."'";
		$map['requisition.status'] = count(getRequisitionStatus());
		$map['requisition.des_logcenter'] = $filter['filter_logcenter'];
		$des_requisition_operation = M('requisition_product')
		->alias('rp')
		->join('left join `requisition` ON requisition.id=rp.requisition_id')
		->join('left join `logcenters` lg ON requisition.user_id=lg.user_id')
		->where($map)
		->field('qty,name,unit_price,option_name,packing_no,deliver_time,(SELECT logcenter_name from logcenters where logcenter_id=requisition.src_logcenter ) as src_logcenter_name,(SELECT logcenter_name from logcenters where logcenter_id=requisition.des_logcenter ) as des_logcenter_name')
		->order('option_name,deliver_time')
		->select();
		return $des_requisition_operation;
	}

	public function getAllList($filter){
		$requisitionlist = M('requisition')
		->join('(select vendors.user_id,  vendor_name from vendors) v on v.user_id = requisition.vendor_id','left')
		->join('(select count(1) as count, requisition_id from requisition_product group by requisition_id) ppc on ppc.requisition_id = requisition.id','left')
		->join('(select user_id, company as logcenter_company, address_1 as logcenter_address, logcenter_name from logcenters) lg on lg.user_id = requisition.user_id','left')
		->order('requisition.id DESC')
		->where($filter)->select();
		return $requisitionlist;
	}
	public function getAllProductList($filter){
		$rolist = M('requisition_product')
		->alias('rp')
		//->join('(select vendors.user_id,  vendor_name from vendors) v on v.user_id = ro.vendor_id','left')
		->join('left join `requisition` ro ON ro.id=rp.requisition_id')
		// ->join('(select user_id, company as logcenter_company, address_1 as logcenter_address, logcenter_name from logcenters) lg on lg.user_id = ro.user_id','left')
		->join('left join `product` p ON p.product_id=rp.product_id')
		->join('left join `vendor` v ON v.vproduct_id=rp.product_id')
		->join('left join `vendors` vs ON vs.vendor_id=v.vendor')	
		->field('rp.name,rp.sku,rp.option_name,rp.qty,ro.date_added,ro.deliver_time,p.model,ro.status,p.price as price,rp.unit_price,p.product_id,rp.requisition_id,vs.vendor_name,rp.packing_no,ro.des_logcenter,(select logcenter_name from `logcenters` lg where lg.logcenter_id=ro.des_logcenter) as des,(select logcenter_name from `logcenters` lg where lg.logcenter_id=ro.src_logcenter) as src')
		->order('ro.id DESC')	
		->where($filter)
		->select();
		return $rolist;
	}
	public function getListCount($filter){
		$count = M('requisition')
		->join('vendors v on v.user_id = requisition.vendor_id','left')
		->where($filter)
		->count();
		return $count;
	}
	public function getProducts($fitler){
		$res = M('product')->join('product_description pd on pd.product_id = product.product_id')
		->field('product.*, pd.*, vendor.product_cost')->join('vendor on vendor.vproduct_id = product.product_id')
		->where($fitler)
		->where(array('pd.language_id'=>(int)$this->config->get('config_language_id')))
		->limit(5)->select();
		return $res;
	}
	public function getRequisition($id){
		$requisition = M('requisition')->find($id);
		if($requisition){
			$out_warehouse_id = $requisition['out_warehouse_id'];
			$in_warehouse_id = $requisition['in_warehouse_id'];
			$user = M('user')->find($requisition['user_id']);
			$requisition['creator'] = $user;

			$map['warehouse_id'] = $out_warehouse_id;
			$out_warehouse_info = M('warehouse')->where($map)->find();
			$requisition['out_warehouse_info'] = $out_warehouse_info;

			$map['warehouse_id'] = $in_warehouse_id;
			$in_warehouse_info = M('warehouse')->where($map)->find();
			$requisition['in_warehouse_info'] = $in_warehouse_info;
		}

		return $requisition;
	}
	public function getRequisitionProducts($id){
		$where['requisition_id'] = $id;
		$products = M('requisition_product')->where($where)->select();
		return $products;
	}
	public function getRequisitionHistory($id){
		$where['requisition_id'] = $id;
		$history = M('requisition_history')->where($where)->select();
		return $history;
	}
	public function getVendors($filter_name=''){
		return M('vendors')->limit(5)->where(array('vendor_name'=>array('like', "%$filter_name%")))->select();
	}
	public function addRequisition($data, $user_id){
		$requisition_data['user_id'] = $user_id;
		$requisition_data['date_added'] = date('Y-m-d H:i:s', time());
		$requisition_data['status'] = 1;
		$requisition_data['out_warehouse_id'] = $data['out_warehouse_id'];
		$requisition_data['in_warehouse_id'] = $data['in_warehouse_id'];
		$requisition_data['included_order_ids'] = $data['included_order_ids'];
		$total = 0;
		foreach ($data['products'] as $key => $product) {
			$total += $product['qty']*$product['unit_price'];
		}
		$requisition_data['total'] = $total;

		if(!$data['id']){
			$requisition_id = M('requisition')->add($requisition_data);
		}
		foreach ($data['products'] as $key => $product) {
			$product_data['requisition_id'] = $requisition_id;
			$product_data['product_id'] = $product['product_id'];
			$product_data['qty'] = $product['qty'];
			$product_data['delivered_qty'] = $product['delivered_qty']?$product['delivered_qty']:0;
			$product_data['unit_price'] = $product['unit_price'];
			$product_data['price'] = $product_data['qty']* $product_data['unit_price'];
			$product_data['status'] = $product['status']?$product['status']:0;
			$product_data['name'] = $product['name'];
			$product_data['requisition_status'] = $product['requisition_status'];
			$product_data['product_code'] = $product['product_code'];
			$product_data['sku'] = $product['sku'];
			$product_data['comment'] = $product['comment'];
			$product_data['sort_order'] = 0;
			$product_data['option_id'] = $product['option']['product_option_value_id'];
			$product_data['option_name'] = $product['option']['name'];
			$product_data['packing_no'] = M('product')->where('product_id ='.$product['product_id'])->getField('packing_no');
			M('requisition_product')->add($product_data);
		}
		return $requisition_id;
	}

	public function deleteRequisition($id) {
		$where['requisition_id'] = $id;
		$products = M('requisition_product');
		$products->where($where)->delete();
		$requisition = M('requisition');
		$requisition->where(array('id'=>$id))->delete();
	}

	public function setStock($requisition_id, $to_save_stock) {
		//添加入库历史记录
		
		//修改ro单的delivered_qty
		$requisition_product = M('requisition_product');
		foreach ($to_save_stock as $requisition_product_id => $qty) {
			$data['qty'] = $qty;
			$requisition_product->where(array('id'=>$requisition_product_id, 'requisition_id'=>$requisition_id))->setInc('qty', $qty);
			$requisition_product_name = $requisition_product->where(array('id'=>$requisition_product_id, 'requisition_id'=>$requisition_id))->getField('name');
			$comment = "操作数量："."<br/>".$requisition_product_name." ： 	".$qty;
			$this->addRequisitionHistory($requisition_id, $this->user->getId(), $this->user->getUserName(), $comment);
		}
	}

	public function updateRequisitionAcceptTime($id){
		$requisitionModel = M('requisition');
		$data['deliver_time'] = date('Y-m-d h:i:s',time());
		$where['id'] = $id;
		$requisitionModel
		->where($where)
		->data($data)
		->save();
	}

	public function setComment($requisition_id, $to_save_comment) {
		//添加入库历史记录

		//修改ro单的delivered_qty
		$requisition_product = M('requisition_product');
		foreach ($to_save_comment as $requisition_product_id => $comment) {
			$data['comment'] = $comment;
			$requisition_product
			->where(array('id'=>$requisition_product_id, 'requisition_id'=>$requisition_id))
			->data($data)
			->save();
			
		}
	}

	public function changeRequisitionStatus($requisition_id, $requisition_status) {
		$requisitionlist = M('requisition');
		$requisitionlist->data(array('id'=>$requisition_id, 'status'=>$requisition_status))->save();
	}

	public function findOrderWithoutVendor($orders) {
		$order_query = M('order_product op')
		->join('left join vendor v on v.vproduct_id=op.product_id')
		->where('op.order_id in (' . implode(',', $orders) . ') AND v.vendor is null')
		->field('v.vendor as vendor_id, op.name as name')
		->select();
		return $order_query;
	}

	public function getUserIdByVendorId($vendor_id) {
		$query = M('vendors')
		->where('vendor_id='.$vendor_id)
		->field('user_id')
		->find();
		if($query) {
			return $query['user_id'];		
		} else {
			return 0;
		}
	}

	public function getVendorIdsFromOrders($orders) {
		$order_query = M('order_product op')
		->join('vendor v on v.vproduct_id=op.product_id')
		->where('op.order_id in (' . implode(',', $orders) . ')')
		->field('v.vendor as vendor_id')
		->group('v.vendor')
		->select();
		return $order_query;
	}

	public function getPoDataFromOrders($orders, $vendor_id=0) {
		$order_query = M('order_product op')
		->join('left join order_option oo on oo.order_product_id=op.order_product_id')
		->join('vendor v on v.vproduct_id=op.product_id')
		->where('op.order_id in (' . implode(',', $orders) . ') AND v.vendor="' . $vendor_id . '"')
		->group('op.product_id, oo.product_option_value_id')
		->field('v.vendor as vendor_id, op.product_id as product_id, v.product_cost as unit_price, 
			v.product_cost*op.quantity as price, op.name as name, oo.product_option_value_id as option_id, oo.value as option_name, "" as sku, "" as comment, SUM(op.quantity) as qty, op.order_id')
		->select();
		return $order_query;
	}

	public function setPoGeneratedFlag($orders) {
		foreach ($orders as $order_id) {
			$Order = M('order');
			$Order->where(array('order_id'=>$order_id))->setInc('po_generated');
		}
	}

	public function getToBillPo($cur_month, $end_month, $billed_po_ids, $vendor_id = 0) {
		$complete_status = array('4','6'); // 4=>'已收货', 6=>'审核完成'
		$map['date_added'] = array('between',array($end_month, $cur_month));
		$map['status'] = array('in', $complete_status);
		if(!empty($billed_po_ids)) {
			$map['id'] = array('not in', $billed_po_ids);	
		}
		$map['vendor_id'] = array('eq', $vendor_id);
		$query = M('po')->where($map)->select();
		return $query;
	}

	public function getVendorIdsFromPo($cur_month, $end_month, $billed_po_ids) {
    $complete_status = array('4','6'); // 4=>'已收货', 6=>'审核完成'
		$map['date_added'] = array('between',array($end_month, $cur_month));
		$map['status'] = array('in', $complete_status);
		if(!empty($billed_po_ids)) {
			$map['id'] = array('not in', $billed_po_ids);	
		}
		$query = M('po')->where($map)->group('vendor_id')->field('vendor_id')->select();
		return $query;
  }

  public function addRequisitionHistory($requisition_id, $user_id, $operator_name, $comment) {
  	$requisitionHistory = M('requisition_history');
  	$data = array('requisition_id'=>$requisition_id, 'user_id'=>$user_id, 
  		'operator_name'=>$operator_name, 'comment'=>$comment, 
  		'notify'=>0, 'date_added'=>date('Y-m-d H:i:s'));
  	$requisitionHistory->data($data)->add();
  }

  public function setPoTotal($id, $new_total) {
  	$requisition = M('po');
		$requisition->where(array('id'=>$id, 'status'=>3))->data(array('total'=>$new_total))->save();
  }

  public function minusProductInitiai($requisition_id){
  	$requisition_product_model = M('requisition_product');
  	$map['ro.id'] = $requisition_id;
  	$requisition_product_data = $requisition_product_model
  	->alias('rp')
  	->join('left join `requisition` ro ON ro.id=rp.requisition_id')
  	->field('rp.product_id,rp.option_id,src_logcenter,des_logcenter,rp.qty')
  	->where($map)
  	->select();
  	$product_initiai_model = M('product_initiai');
  	foreach ($requisition_product_data as $key => $value) {
  		$initiai_map['option_id'] = $value['option_id']?$value['option_id']:-1;
  		$initiai_map['product_id'] = $value['product_id'];
  		$initiai_map['logcenter_id'] = $value['src_logcenter'];
  		$initiai_data = $product_initiai_model->where($initiai_map)->find();
  		if($initiai_data){
  			$edit_initiai_data['real_initiai'] = $initiai_data['real_initiai'] - $value['qty'];
  			$product_initiai_model->data($edit_initiai_data)->where($initiai_map)->save();
  		}else{
  			$edit_initiai_data['real_initiai'] = 0 - $value['qty'];
  			$edit_initiai_data['option_id'] = $value['option_id']?$value['option_id']:-1;
  			$edit_initiai_data['product_id'] = $value['product_id'];
  			$edit_initiai_data['logcenter_id'] = $value['src_logcenter'];
  			$edit_initiai_data['date_added'] = date('Y-m-d h:i:s',time());
  			$edit_initiai_data['initial_number'] = 0 ;
  			$product_initiai_model->data($edit_initiai_data)->add();
  		}
  	}
  }

  public function addProductInitiai($requisition_id){
  	$this->log->write('model two'.$requisition_id);
  	$requisition_product_model = M('requisition_product');
  	$map['ro.id'] = $requisition_id;
  	$requisition_product_data = $requisition_product_model
  	->alias('rp')
  	->join('left join `requisition` ro ON ro.id=rp.requisition_id')
  	->field('rp.product_id,rp.option_id,src_logcenter,des_logcenter,rp.qty')
  	->where($map)
  	->select();
  	$this->log->write('select three'.$requisition_id);
  	$product_initiai_model = M('product_initiai');
  	foreach ($requisition_product_data as $key => $value) {
  		$initiai_map['option_id'] = $value['option_id']?$value['option_id']:-1;
  		$initiai_map['product_id'] = $value['product_id'];
  		$initiai_map['logcenter_id'] = $value['des_logcenter'];
  		$initiai_data = $product_initiai_model->where($initiai_map)->find();
  		if($initiai_data){
  			$edit_initiai_data['real_initiai'] = $initiai_data['real_initiai'] + $value['qty'];
  			$product_initiai_model->data($edit_initiai_data)->where($initiai_map)->save();
  		}else{
  			$edit_initiai_data['real_initiai'] = $value['qty'];
  			$edit_initiai_data['option_id'] = $value['option_id']?$value['option_id']:-1;
  			$edit_initiai_data['product_id'] = $value['product_id'];
  			$edit_initiai_data['logcenter_id'] = $value['des_logcenter'];
  			$edit_initiai_data['date_added'] = date('Y-m-d h:i:s',time());
  			$edit_initiai_data['initial_number'] = 0 ;
  			$product_initiai_model->data($edit_initiai_data)->add();
  		}
  	}
  }

	//判断是否生成出入库单
	public function getRequisitionStock($id){
		$where['si.refer_id'] = $id;
		$where['si.refer_type_id'] = 4;
		$stock = M('stock_in')
			->alias('si')
			->join(' `warehouse` w on w.warehouse_id = si.warehouse_id','left')
			->join(' `user` u on u.user_id = si.user_id','left')
			->join(' `inout_state` ios on ios.id = si.status','left')
			->field('si.*,w.name as warehouse_name,u.fullname as user_name,ios.name as status_name')
			->where($where)->select();
		return $stock;
	}

}